import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @author matias
 *
 */
public class ExampleMetaData {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		String driver = "org.postgresql.Driver";
		String url = "jdbc:postgresql://localhost:5432/prueba";
		String username = "postgres";
		String password = "200187";
		DatabaseMetaData metadata;
		ResultSet result;
		try {
			Class.forName(driver);
			Connection connection =	DriverManager.getConnection(url, username, password);
			connection.createStatement().execute("SET SEARCH_PATH = 'EjercicioBanco'");
			metadata = connection.getMetaData();
			String types[] = {"TABLE"};
			System.out.println("TABLES");
			result = metadata.getTables(null, "EjercicioC", null, types);		
			while(result.next()){
				System.out.println("\nTABLE : "+result.getString(3));
				ResultSet r2 = metadata.getColumns(null, "EjercicioC", result.getString(3), null);
				System.out.println("	COLUMNS : ");
				while(r2.next()){ // Columns
					System.out.print("		Name : "+r2.getString(4));
					System.out.print("; Type : "+r2.getInt(5));
					System.out.print("; Column Size : "+r2.getInt(7));
					System.out.print("; Is Nullable : "+r2.getInt(11));
					System.out.println("; Is Autoincrement : "+r2.getString(23));
				}
			}
			/* METODO NO IMPLEMENTADO
			System.out.println("FUNCTIONS");
			result = metadata.getFunctions(null, "EjercicioC", null);
			while(result.next())
				System.out.println("Function : "+result.getString(3));
			 */
			System.out.println("\nPROCEDURES");
			result = metadata.getProcedures(null, "EjercicioC", null);
			while(result.next()){
				System.out.println("\nProcedure : "+result.getString(3));
				ResultSet r2 = metadata.getProcedureColumns(null, "EjercicioC", result.getString(3), null);
				System.out.println("	PARAMS : ");
				while(r2.next()){ // Columns
					System.out.print("		Param Name : "+r2.getString(4));
					System.out.print("; Param Type : "+r2.getInt(5));
					System.out.println("; Data Type : "+r2.getInt(6));
				}
			}

			System.out.println("\nFOREIGN KEYS");
			ResultSet r2 = metadata.getCrossReference(null, "EjercicioBanco", null, null, null, null);
			while(r2.next()){ // Columns
				System.out.println("\n");
				System.out.println(r2.getString(1));
				System.out.println(r2.getString(2));
				System.out.println(r2.getString(3));
				System.out.println(r2.getString(4));
				System.out.println(r2.getString(5));
				System.out.println(r2.getString(6));
				System.out.println(r2.getString(7));
				System.out.println(r2.getString(8));
				System.out.println(r2.getString(9));
				System.out.println(r2.getString(10));
				System.out.println(r2.getString(11));
				System.out.println(r2.getString(12));
				System.out.println("\n");
			}

			// CHECKS
			System.out.print("\n");
			ResultSet r3 = connection.createStatement().executeQuery("Select * from information_schema.check_constraints where constraint_schema = 'EjercicioC'");
			while(r3.next()){
				String constraint_name = r3.getString(3);
				if(!constraint_name.startsWith("17093")){
					System.out.print("Constraint name : "+r3.getString(3));
					System.out.println("; Check Clause : "+r3.getString(4));
				}
			}

			System.out.print("\n");
			ResultSet r4 = connection.createStatement().executeQuery("Select * from information_schema.column_domain_usage where domain_schema = 'EjercicioC'");
			while(r4.next()){
				System.out.print("Domain name : "+r4.getString(3));
				System.out.print("; Table name : "+r4.getString(6));
				System.out.println("; Column name : "+r4.getString(7));
			}

			System.out.print("\n");
			ResultSet r5 = connection.createStatement().executeQuery("select * from information_schema.constraint_table_usage where table_schema = 'EjercicioC'");
			while(r5.next()){
				System.out.print("Table name : "+r5.getString(3));
				System.out.println("; Constraint name : "+r5.getString(6));
			}

			// REFERENCIAS
			System.out.print("\n");
			ResultSet r6 = connection.createStatement().executeQuery("select * from information_schema.referential_constraints where constraint_schema = 'EjercicioC'");
			while(r6.next()){
				System.out.print("Constraint name : "+r6.getString(3));
				System.out.print("; Unique Constraint name : "+r6.getString(6));
				System.out.print("; Update rule : "+r6.getString(8));
				System.out.println("; Delete rule : "+r6.getString(9));
			}

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}
